package org.misalen.web.database.repository.mysql;

import java.math.BigDecimal;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.transform.AliasToBeanResultTransformer;
import org.misalen.utils.TextUtil;
import org.misalen.web.database.dao.mysql.impl.BaseMySqlDaoImpl;
import org.misalen.web.database.model.mysql.pro.ProMapAdvertising;
import org.misalen.web.database.util.PaginationFrom;
import org.springframework.stereotype.Repository;

@Repository
public class ProMapAdvertisingRepository extends BaseMySqlDaoImpl<ProMapAdvertising> {
	public List<ProMapAdvertising> findBydistance(BigDecimal latitude, BigDecimal longitude, String userId) {
		Session session = this.openSession();
		String sql = "select A.carrierUrl,A.primaryKey,A.longitude,A.adverSlogan,A.latitude,A.adverImagesUrl,CONVERT(sum(CASE WHEN B.userId = '" + userId
				+ "' THEN 1 ELSE 0 END),CHAR) isGet,CONVERT(sum(CASE WHEN B.userId = '" + userId
				+ "' THEN B.money ELSE NULL END),SIGNED) getMoney from ProMapAdvertising A LEFT JOIN "
				+ "ProMapReward B ON A.primaryKey = B.mapId where A.grabThe='0' and A.state='generalize' and sqrt("
				+ " (((?0-A.longitude)*PI()*12656*cos(((?1+A.latitude)/A.promotionDistance/1000)*PI()/180)/180) "
				+ " * ((?0-A.longitude)*PI()*12656*cos (((?1+A.latitude)/A.promotionDistance/1000)*PI()/180)/180) "
				+ " ) + ( ((?1-A.latitude)*PI()*12656/180) * ((?1-A.latitude)*PI()*12656/180))  "
				+ " )<A.promotionDistance/1000 GROUP BY A.primaryKey";
		SQLQuery query = session.createSQLQuery(sql);
		query.setParameter("0", longitude);
		query.setParameter("1", latitude);
		query.setResultTransformer(new AliasToBeanResultTransformer(ProMapAdvertising.class));
		@SuppressWarnings("unchecked")
		List<ProMapAdvertising> list = query.list();
		closeSession(session);
		return list;
	}

	public ProMapAdvertising getValid(String mapId) {
		String hql = "from ProMapAdvertising where primaryKey=?0 and grabThe=?1";
		return get(hql, new Object[] { mapId, TextUtil.booleanToString(false) });
	}

	public void updateGrabThe(String grabThe, String mapId) {
		String sql = "update ProMapAdvertising set grabThe='" + grabThe + "' where primaryKey='" + mapId + "'";
		Session session = this.openSession();
		Query query = session.createQuery(sql);
		query.executeUpdate();
		closeSession(session);
	}
	public PaginationFrom<ProMapAdvertising> findByUser(String userId, String state,
			PaginationFrom<ProMapAdvertising> pagination) {
		StringBuffer selectBuffer = new StringBuffer();
		selectBuffer.append("SELECT A.primaryKey,A.addTime,A.adverSlogan,A.adverImagesUrl,A.promotionExpenses,A.promotionNumber");
		selectBuffer.append(" FROM ProMapAdvertising A ");
		selectBuffer.append(" WHERE A.state = '" + state + "' ");
		selectBuffer.append(" AND A.userId = '" + userId + "' ");
		selectBuffer.append(" ORDER BY A.addTime desc");
		Session session = this.openSession();
		SQLQuery query = session.createSQLQuery(selectBuffer.toString());
		query.setResultTransformer(new AliasToBeanResultTransformer(ProMapAdvertising.class));
		StringBuffer countBuffer = new StringBuffer();
		countBuffer.append("select count(*) from (" + selectBuffer + ") T");
		pagination.setTotal(Long.valueOf(session.createSQLQuery(countBuffer.toString()).uniqueResult().toString()));
		setResultIndex(query, pagination.getPage(), pagination.getRows());
		@SuppressWarnings("unchecked")
		List<ProMapAdvertising> list = query.list();
		pagination.setList(list);
		closeSession(session);
		return pagination;
	}

}